import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.types import VARCHAR


# class ConfSql():
#
#     def __init__(self):
#         # conn = create_engine(r'mysql+pymysql://root:mySQL.root.4415@localhost:3306/Stock?charset=utf8')
#         self.conn = pymysql.connect(host='localhost', user='root',
#                                     passwd='mySQL.root.4415', charset='utf8')
#         self.cursor = self.conn.cursor()
#
#     def create_table(self, name):
#         self.cursor.execute('use Stock;')
#         lang = """
#         create table if not exists {} (
#         `id` int(11) not null AUTO_INCREMENT,
#         `ts_code` varchar(12) default '' comment '股票代码',
#         `trade_date` varchar(10) default '' comment '交易日期',
#         `open` decimal(10,2) DEFAULT '0.00' COMMENT '开盘价',
#         `high` decimal(10,2) DEFAULT '0.00' COMMENT '最高价',
#         `low` decimal(10,2) DEFAULT '0.00' COMMENT '最低价',
#         `close` decimal(10,2) DEFAULT '0.00' COMMENT '收盘价',
#         `pre_close` decimal(10,2) DEFAULT '0.00' COMMENT '昨日收盘价',
#         `change` decimal(10,2) DEFAULT '0.00' COMMENT '价格变化',
#         `pct_chg` double(16,4) DEFAULT '0.0000' COMMENT '涨跌幅',
#         `vol` decimal(10,2) DEFAULT '0.00' COMMENT '成交量（手）',
#         `amount` double(16,4) DEFAULT '0.0000' COMMENT '成交额（千元）',
#         PRIMARY KEY (`id`),
#         UNIQUE KEY `uni_key` (`trade_date`,`ts_code`) USING BTREE,
#         KEY `ts_code` (`ts_code`) USING BTREE)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#         """
#         self.cursor.execute(lang.format(name))
#         self.cursor.close()
#         self.conn.close()
class ConfSQL(object):

    def __init__(self):
        # print('正在建立数据库链接')
        self.engine = create_engine(r'mysql+pymysql://root:mySQL.root.4415@localhost:3306/Stock?charset=utf8')
        # print('数据库链接成功')
        self.dtypes = {'ts_code': VARCHAR(20, 0), 'trade_date': VARCHAR(20, 0)}
        self.sql_query = 'select * from {} order by trade_date DESC limit {};'
        self.select_date = 'select * from {} where UNIX_TIMESTAMP(trade_date) < UNIX_TIMESTAMP(\'{}\') ' \
                           'and UNIX_TIMESTAMP(trade_date)  >= UNIX_TIMESTAMP(\'{}\') order by trade_date desc;'

    def to_sql(self, obj, name, exists):
        obj.to_sql(name, self.engine, index=True, dtype=self.dtypes, if_exists=exists)

    def read_sql(self, name, count):
        _flag = pd.read_sql(self.sql_query.format(name, count), self.engine)
        return _flag

    def put_sql(self, obj, name, exists):
        obj.to_sql(name, self.engine, index=True, if_exists=exists)

    def get_sql(self, sql):
        _flag = pd.read_sql(sql, self.engine)
        return _flag

    def read_date_sql(self, name, start_date, end_date):
        _flag = pd.read_sql(self.select_date.format(name, start_date, end_date), self.engine)
        return _flag
